﻿/***************************************************************************
    Tarih           Sürüm       Değişiklik          İşlem
    --------------------------------------------------------------------------
    2013.Kas        1           Mehmet A. ORAKCI  Oluşturuldu.

****************************************** mehmetakiforakci@gmail.com ****/

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;

namespace RaporProj
{
    public class ReportDataProvider : IReportProvider
    {
        DataProviderBase DataProvider = DataProviderFactory.CreateSqlDataProvider();

        public IEnumerable<IReport> GetReports()
        {
            ICollection<IReport> reports = new List<IReport>();

            DbDataReader reader = DataProvider.Read(@"
                SELECT r.ReportId, r.CreationTime, r.IsGroup, r.ParentId, r.IsDeleted, r.Name, /* r.FileName, r.FileData, */ c.Name AS CategoryName
                FROM dbo.Reports r 
                LEFT JOIN dbo.Reports c ON r.ParentId = c.ReportId
                WHERE r.IsDeleted = 0
                ORDER BY c.SortIndex, r.SortIndex");

            while (reader.Read())
            {
                reports.Add(new Report
                {
                    Id = Convert.ToInt32(reader["ReportId"]),
                    IsGroup = Convert.ToBoolean(reader["IsGroup"]),
                    ParentId = Convert.ToInt32(reader["ParentId"]),
                    IsDeleted = Convert.ToBoolean(reader["IsDeleted"]),
                    CreationTime = Convert.ToDateTime(reader["CreationTime"]),
                    Name = Convert.ToString(reader["Name"])
                });
            }
            return reports;
        }

        public IReport GetReport(Int32 reportId, Boolean getFileData)
        {
            Report report = null;

            String sql = "SELECT TOP 1 r.ReportId, r.CreationTime, r.IsGroup, r.ParentId, r.IsDeleted, r.Name, r.FileName, c.Name AS CategoryName";
            if (getFileData)
                sql += ", r.FileData";
            sql += @"
                FROM dbo.Reports r 
                INNER JOIN dbo.Reports c ON r.ParentId = c.ReportId
                WHERE r.IsDeleted = 0
                    AND c.IsDeleted = 0
                    AND c.IsGroup = 1
                    AND r.IsGroup = 0
                    AND r.ReportId = {0}";

            DbDataReader reader = DataProvider.Read(sql, reportId);
            if (reader.Read())
            {
                report = new Report
                {
                    Id = Convert.ToInt32(reader["ReportId"]),
                    IsGroup = Convert.ToBoolean(reader["IsGroup"]),
                    ParentId = Convert.ToInt32(reader["ParentId"]),
                    IsDeleted = Convert.ToBoolean(reader["IsDeleted"]),
                    CreationTime = Convert.ToDateTime(reader["CreationTime"]),
                    Name = Convert.ToString(reader["Name"]),
                    CategoryName = Convert.ToString(reader["CategoryName"]),
                    FileName = Convert.ToString(reader["FileName"])
                };

                if (getFileData)
                    report.FileData = reader["FileData"] == DBNull.Value ? null : (byte[])reader["FileData"];
            }
            return report;
        }

        public Boolean AddReport(String name, Int32 categoryId, String fileName, Byte[] fileData)
        {
            // Report.FileData = File.ReadAllBytes(Report.FileName)
            var reader = DataProvider.Read(@"
                DECLARE @SortIndex INT = NULL

                SELECT @SortIndex = MAX(SortIndex) 
                FROM dbo.Reports 
                WHERE ParentId = {1}

                INSERT INTO dbo.Reports (Name, ParentId, FileName, FileData, SortIndex) 
                    VALUES ({0}, {1}, {2}, {3}, ISNULL(@SortIndex, 0) + 1)

                SELECT ISNULL(SCOPE_IDENTITY(),0) AS Id
                ", name, categoryId, fileName, fileData);

            if (reader.Read())
            {
                return Convert.ToInt32(reader[0]) > 0;
            }
            return false;
        }

        public Boolean UpdateReport(Int32 reportId, String newName, Int32 categoryId, String newFileName, Byte[] newFileData)
        {
            return DataProvider.Execute(@"
                DECLARE @SortIndex INT = NULL
                DECLARE @Parent INT = 0

                SELECT @Parent = ParentId FROM dbo.Reports WHERE ReportId = {0}
                IF (@Parent <> {2})
                    SELECT @SortIndex = MAX(SortIndex) FROM dbo.Reports WHERE ParentId = {2}
                
                UPDATE dbo.Reports 
                SET Name = {1}, ParentId = {2}, FileName = {3}, FileData = {4}, SortIndex = CASE WHEN @OldParent = {2} THEN SortIndex ELSE ISNULL(@SortIndex, 0) + 1 END
                WHERE ReportId = {0} AND IsGroup = 0
                ", reportId, newName, categoryId, newFileName, newFileData) > 0;
        }

        public Boolean UpdateReport(Int32 reportId, String newName, Int32 categoryId)
        {
            return DataProvider.Execute(@"
                DECLARE @SortIndex INT = NULL
                DECLARE @OldParent INT = 0

                SELECT @OldParent = ParentId FROM dbo.Reports WHERE ReportId = {0}
                IF (@OldParent <> {2})
                    SELECT @SortIndex = MAX(SortIndex) FROM dbo.Reports WHERE ParentId = {2}
                    
                UPDATE dbo.Reports 
                SET Name = {1}, ParentId = {2}, SortIndex = CASE WHEN @OldParent = {2} THEN SortIndex ELSE ISNULL(@SortIndex, 0) + 1 END
                WHERE ReportId = {0} AND IsGroup = 0
                ", reportId, newName, categoryId) > 0;
        }

        public Boolean DeleteReport(Int32 reportId)
        {
            return DataProvider.Execute(@"
                UPDATE R 
                SET IsDeleted = 1
                FROM dbo.Reports R 
                WHERE ReportId = {0} 
                    AND IsGroup = 0
                ", reportId) > 0;
        }

        public Boolean DeleteCategory(Int32 categoryId)
        {
            return DataProvider.Execute(@"
			    DECLARE @ReportCount INT = 0

			    SELECT @ReportCount = COUNT(0)
                FROM dbo.Reports R 
			    INNER JOIN dbo.Reports C ON R.ParentId = C.ReportId
			    WHERE C.ReportId = {0} 
                    AND R.IsDeleted = 0
                GROUP BY C.ReportId

			    IF (@ReportCount = 0)
			    BEGIN 
				    UPDATE R
				    SET IsDeleted = 1
				    FROM dbo.Reports R
				    WHERE ReportId = {0} 
                        AND IsGroup = 1
			    END ", categoryId) > 0;
        }

        public Boolean AddCategory(String name)
        {
            return DataProvider.Execute(@"
                DECLARE @SortIndex INT = NULL

                SELECT @SortIndex = MAX(SortIndex) 
                FROM dbo.Reports 
                WHERE IsGroup = 1

                INSERT INTO dbo.Reports (Name, IsGroup, ParentId, SortIndex) 
                    VALUES ({0}, 1, 0, ISNULL(@SortIndex, 0) + 1)
                ", name) > 0;
        }

        public IEnumerable<ICategory> GetCategories()
        {
            ICollection<ICategory> reports = new List<ICategory>();

            DbDataReader reader = DataProvider.Read(@"
                SELECT r.ReportId, r.CreationTime, r.ParentId, r.IsDeleted, r.Name
                FROM dbo.Reports r 
                WHERE r.IsDeleted = 0
                    AND r.IsGroup = 1 ");
            while (reader.Read())
            {
                reports.Add(new Category
                {
                    Id = Convert.ToInt32(reader["ReportId"]),
                    IsDeleted = Convert.ToBoolean(reader["IsDeleted"]),
                    CreationTime = Convert.ToDateTime(reader["CreationTime"]),
                    CategoryName = Convert.ToString(reader["Name"])
                });
            }
            return reports;
        }

        public Boolean RenameCategory(Int32 categoryId, String newName)
        {
            return DataProvider.Execute(@"
                UPDATE dbo.Reports 
                SET Name = {1}
                WHERE ReportId = {0}
                ", categoryId, newName) > 0;
        }

        public ICategory GetCategory(Int32 categoryId)
        {
            DbDataReader reader = DataProvider.Read(@"
                SELECT TOP 1 c.ReportId, c.CreationTime, c.IsDeleted, c.Name AS CategoryName
                FROM dbo.Reports c
                WHERE c.IsDeleted = 0
                    AND c.IsGroup = 1
                    AND c.ReportId = {0}", categoryId);

            if (reader.Read())
            {
                return new Category
                {
                    Id = Convert.ToInt32(reader["ReportId"]),
                    IsDeleted = Convert.ToBoolean(reader["IsDeleted"]),
                    CreationTime = Convert.ToDateTime(reader["CreationTime"]),
                    CategoryName = Convert.ToString(reader["CategoryName"]),
                };
            }
            return null;
        }

    }
}
